﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Common.Logging;


namespace Utility
{
    public static class SqlHelper
    {
        static string ConnectionString;

        static SqlHelper()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
        }

        public static void ExecuteStoredProcedure(string spName)
        {
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = spName;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public static void ExecuteNonQuery(string sql)
        {
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public static IDataReader ExecuteReader(string sql)
        {
            IDataReader dbReader = null;
            using (DbConnection conn = new SqlConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    dbReader = command.ExecuteReader();
                }
                catch
                {
                    throw;
                }
            }
            return dbReader;
        }

        public static void ExecuteNonQuery(string sql, SqlParameter[] prams)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {

                var _sql_command = new SqlCommand(sql, conn);
                _sql_command.CommandType = CommandType.Text;
                _sql_command.Parameters.Clear();
                foreach (var pram in prams)
                    _sql_command.Parameters.Add(pram);
                try
                {
                    conn.Open();
                    _sql_command.ExecuteNonQuery();
                }
                catch (SqlException sqlex)
                {
                    ILog log = LogFactory.CreateFileLog();
                    log.Error(sqlex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}
